/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Golf_Manager.sqlite.sql;

import Golf_Manager.vo.Member;

/**
 *
 * @author Administrator
 */
public class MemberSql {
    
    public static StringBuffer getSelectMemberSql(StringBuilder whrerSql, String orderString) {
        
        StringBuffer selectMemberSql = new StringBuffer();
        
        selectMemberSql.append("SELECT");
        selectMemberSql.append(" MEMBER_SEQ, ");
        selectMemberSql.append(" NAME, ");
        selectMemberSql.append(" BIRTH, ");
        selectMemberSql.append(" AGE, ");
        selectMemberSql.append(" GENDER, ");
        selectMemberSql.append(" COMPANYNAME, ");
        selectMemberSql.append(" HAND, ");
        selectMemberSql.append(" GRADE, ");
        selectMemberSql.append(" JOINYN, ");
        selectMemberSql.append(" NOTE1, ");
        selectMemberSql.append(" NOTE2, ");
        selectMemberSql.append(" NOTE3 ");
        selectMemberSql.append("FROM MEMBER ");
        selectMemberSql.append("WHERE 1=1 ");
        
        if(whrerSql.toString().length() > 0){
            selectMemberSql.append(whrerSql);
        }
        
        if(orderString != null && !orderString.equals("")){
            selectMemberSql.append("ORDER BY ").append(orderString);
        } else {
            selectMemberSql.append("ORDER BY MEMBER_SEQ DESC");
        }
        
        //System.out.println(selectMemberSql.toString());
        return selectMemberSql;
    }
    
    public static StringBuffer getInsertMemberSql(Member insertMember) {
        
        StringBuffer insertMemberSql = new StringBuffer();
        
        insertMemberSql.append("INSERT INTO MEMBER (MEMBER_SEQ,  NAME,  BIRTH,  AGE,  GENDER,  COMPANYNAME,  HAND,  GRADE,  JOINYN, NOTE1, NOTE2, NOTE3 ) ");
        insertMemberSql.append("VALUES ((SELECT IFNULL(MAX(MEMBER_SEQ) + 1, 1) FROM MEMBER), ");
        if(insertMember.getName() != null && !insertMember.getName().equals("")){
            insertMemberSql.append("'").append(insertMember.getName()).append("', ");
        } else {
            insertMemberSql.append(" null, ");
        }
        if(insertMember.getBirth() != null && insertMember.getBirth().trim().length() == 10){
            insertMemberSql.append("'").append(insertMember.getBirth()).append("', ");
        } else {
            insertMemberSql.append(" null, ");
        }
        if(insertMember.getAge() != null && !insertMember.getAge().equals("")){
            insertMemberSql.append("'").append(insertMember.getAge()).append("', ");
        } else {
            insertMemberSql.append(" null, ");
        }
        if(insertMember.getGender() != null && !insertMember.getGender().equals("")){
            insertMemberSql.append("'").append(insertMember.getGender()).append("', ");
        } else {
            insertMemberSql.append(" null, ");
        }
        if(insertMember.getCompanyName() != null && !insertMember.getCompanyName().equals("")){
            insertMemberSql.append("'").append(insertMember.getCompanyName()).append("', ");
        } else {
            insertMemberSql.append(" null, ");
        }
        if(insertMember.getHand() != null && !insertMember.getHand().equals("")){
            insertMemberSql.append("'").append(insertMember.getHand()).append("', ");
        } else {
            insertMemberSql.append(" null, ");
        }
        if(insertMember.getGrade() != null && !insertMember.getGrade().equals("")){
            insertMemberSql.append("'").append(insertMember.getGrade()).append("', ");
        } else {
            insertMemberSql.append(" null, ");
        }
        
        if(insertMember.getJoinYn() != null && !insertMember.getJoinYn().equals("")){
            insertMemberSql.append("'").append(insertMember.getJoinYn()).append("', ");
        } else {
            insertMemberSql.append(" null, ");
        }
        
        if(insertMember.getNote1() != null && !insertMember.getNote1().equals("")){
            insertMemberSql.append("'").append(insertMember.getNote1()).append("', ");
        } else {
            insertMemberSql.append(" null, ");
        }
        
        if(insertMember.getNote2() != null && !insertMember.getNote2().equals("")){
            insertMemberSql.append("'").append(insertMember.getNote2()).append("', ");
        } else {
            insertMemberSql.append(" null, ");
        }
        
        if(insertMember.getNote3() != null && !insertMember.getNote3().equals("")){
            insertMemberSql.append("'").append(insertMember.getNote3()).append("')");
        } else {
            insertMemberSql.append(" null)");
        }
        
        
        
        //System.out.println(insertMemberSql.toString());
        return insertMemberSql;
    }
    
    public static StringBuffer getUpdateMemberSql(Member insertMember) {
        
        StringBuffer updateMemberSql = new StringBuffer();
        
        updateMemberSql.append("UPDATE MEMBER SET ");
        
        if(insertMember.getName() != null && !insertMember.getName().equals("")){
            updateMemberSql.append("NAME = '").append(insertMember.getName()).append("', ");
        } 
        
        if(insertMember.getBirth() != null && insertMember.getBirth().trim().length() == 10){
            updateMemberSql.append("BIRTH = '").append(insertMember.getBirth()).append("', ");
        } 
        
        if(insertMember.getAge() != null && !insertMember.getAge().equals("")){
            updateMemberSql.append("AGE = '").append(insertMember.getAge()).append("', ");
        } else {
            updateMemberSql.append("AGE = null, ");
        }
        
        if(insertMember.getGender() != null && !insertMember.getGender().equals("")){
            updateMemberSql.append("GENDER = '").append(insertMember.getGender()).append("', ");
        } else {
            updateMemberSql.append("GENDER = null, ");
        }
        
        if(insertMember.getCompanyName() != null && !insertMember.getCompanyName().equals("")){
            updateMemberSql.append("COMPANYNAME = '").append(insertMember.getCompanyName()).append("', ");
        } else {
            updateMemberSql.append("COMPANYNAME = null, ");
        }
        
        if(insertMember.getHand() != null && !insertMember.getHand().equals("")){
            updateMemberSql.append("HAND = '").append(insertMember.getHand()).append("', ");
        } else {
            updateMemberSql.append("HAND = null, ");
        }
        
        if(insertMember.getGrade() != null && !insertMember.getGrade().equals("")){
            updateMemberSql.append("GRADE = '").append(insertMember.getGrade()).append("', ");
        } 
        
        if(insertMember.getNote1() != null && !insertMember.getNote1().equals("")){
           updateMemberSql.append("NOTE1 = '").append(insertMember.getNote1()).append("', ");
        } else {
            updateMemberSql.append("NOTE1 = '', ");
        }
        
        if(insertMember.getNote2() != null && !insertMember.getNote2().equals("")){
           updateMemberSql.append("NOTE2 = '").append(insertMember.getNote2()).append("', ");
        } else {
            updateMemberSql.append("NOTE2 = '', ");
        }
        
        if(insertMember.getNote3() != null && !insertMember.getNote3().equals("")){
           updateMemberSql.append("NOTE3 = '").append(insertMember.getNote3()).append("', ");
        } else {
            updateMemberSql.append("NOTE3 = '', ");
        }
        
        if(insertMember.getJoinYn() != null && !insertMember.getJoinYn().equals("")){
           updateMemberSql.append("JOINYN = '").append(insertMember.getJoinYn()).append("' ");
        } 
        
        
        updateMemberSql.append("WHERE MEMBER_SEQ = '").append(insertMember.getMemberSeq()).append("'");
        
        //System.out.println(updateMemberSql.toString());
        return updateMemberSql;
    }
    
    public static StringBuffer getDeleteMemberSql(String delMemberSeqs) {
        
        StringBuffer deleteMemberSql = new StringBuffer();
        
        deleteMemberSql.append("DELETE FROM MEMBER ");
        deleteMemberSql.append("WHERE MEMBER_SEQ IN (").append(delMemberSeqs).append(")");
        
        //System.out.println(deleteMemberSql.toString());
        return deleteMemberSql;
    }
    
    public static StringBuffer getTableColumnNm(int columnIndex) {
        StringBuffer coluNm = new StringBuffer();
        switch(columnIndex){
            case 0: coluNm.append("NAME"); break;
            case 1: coluNm.append("BIRTH"); break;
            case 2: coluNm.append("AGE"); break;
            case 3: coluNm.append("GENDER"); break;
            case 4: coluNm.append("COMPANYNAME"); break;
            case 5: coluNm.append("HAND"); break;
            case 6: coluNm.append("GRADE"); break;
            case 7: coluNm.append("JOINYN"); break;
            case 8: coluNm.append("NOTE1"); break;
            case 9: coluNm.append("NOTE2"); break;
            case 10: coluNm.append("NOTE3"); break;
            default: coluNm.append("MEMBER_SEQ"); break; 
        }
        return coluNm;
    }
}

